using Mono.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Reflection;

namespace Pro.Data
{
    public class SqliteSever : DatabaseSever
    {
        SqliteConnection Connection;
        SqliteCommand Command;
        SqliteDataReader Reader;
        bool isOpen;
        public void ConnectDB()
        {
            if (isOpen) return;   
            //Connection = new SqliteConnection(SQLHelper.Ins.SqliteConnectString);
            try
            {
                Connection.Open();
                isOpen = true;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        public void CloseDB()
        {
            if (Connection != null)
            {
                Connection.Clone();
                Connection = null;
            }
            Command = null;
            Reader = null;
            isOpen = false;
        }
        SqliteDataReader ExcuteQuery(string Sql)
        {
            //Tip.Ins.ShowLog(Sql);
            try
            {
                Command = new SqliteCommand(Sql, Connection);
                return Command.ExecuteReader();
            }
            catch (Exception e)
            {
                //Tip.Ins.ShowLog(e.ToString());
                throw e;
            }

        }
        public void CreatTable<T>() where T : BaseTableMode, new()
        {
            T t = new T();
            string sql = "CREATE TABLE " + t.TableName() + "(";
            PropertyInfo[] info = t.GetType().GetProperties();
            for (int i = 0; i < info.Length - 1; i++)
            {
                if (info[i].PropertyType == typeof(String))
                {
                    sql += info[i].Name + " TEXT,";

                }
                else if (info[i].PropertyType == typeof(float))
                {
                    sql += info[i].Name + " REAL,";
                }
                else
                {
                    sql += info[i].Name + " INTEGER,";
                }
            }
            if (info[info.Length - 1].PropertyType == typeof(String))
            {
                sql += info[info.Length - 1].Name + " TEXT);";

            }
            else if (info[info.Length - 1].PropertyType == typeof(float))
            {
                sql += info[info.Length - 1].Name + " REAL);";
            }
            else
            {
                sql += info[info.Length - 1].Name + " INTEGER);";
            }
            ExcuteQuery(sql);
        }
        public void CreatTable(Type type)
        {
            BaseTableMode t = MYReFlection.CreateInstance<BaseTableMode>(type);
            string sql = "CREATE TABLE " + t.TableName() + "(";
            PropertyInfo[] info = t.GetType().GetProperties();
            for (int i = 0; i < info.Length - 1; i++)
            {
                if (info[i].PropertyType == typeof(String))
                {
                    sql += info[i].Name + " TEXT,";

                }
                else if (info[i].PropertyType == typeof(float))
                {
                    sql += info[i].Name + " REAL,";
                }
                else
                {
                    sql += info[i].Name + " INTEGER,";
                }
            }
            if (info[info.Length - 1].PropertyType == typeof(String))
            {
                sql += info[info.Length - 1].Name + " TEXT);";

            }
            else if (info[info.Length - 1].PropertyType == typeof(float))
            {
                sql += info[info.Length - 1].Name + " REAL);";
            }
            else
            {
                sql += info[info.Length - 1].Name + " INTEGER);";
            }
            ExcuteQuery(sql);
        }
        public void WriteTable<T>() where T :  BaseTableMode, new()
        {
            T t = new T();
            foreach (var item in t.Dictionary<T>().Keys)
            {
                T tt = t.Dictionary<T>()[item];
                Insert(tt);
            }
        }
        public void DeleteTable<T>() where T : BaseTableMode, new()
        {
            string sql = "Drop Table " + new T().TableName();
            ExcuteQuery(sql);
        }
        public void Insert<T>(T t) where T : BaseTableMode, new()
        {
            string sql = "INSERT INTO " + t.TableName() + " VALUES (";
            PropertyInfo[] info = t.GetType().GetProperties();
            for (int i = 0; i < info.Length - 1; i++)
            {
                if (info[i].PropertyType == typeof(String))
                {
                    sql += "'" + info[i].GetValue(t) + "',";
                }
                else
                {
                    sql += info[i].GetValue(t) + ",";
                }
            }
            if (info[info.Length - 1].PropertyType == typeof(String))
            {
                sql += "'" + info[info.Length - 1].GetValue(t) + "');";
            }
            else
            {
                sql += info[info.Length - 1].GetValue(t) + ");";
            }
            ExcuteQuery(sql);
        }
        public void DeleteTableInfos<T>() where T : BaseTableMode, new()
        {
            string sql = "Delete From " + new T().TableName().ToString();
            ExcuteQuery(sql);
        }
        public void DeleteByOnlyId<T>(T t) where T : BaseTableMode, new()
        {
            // string  sql = "PRAGMA table_info( " + t.TableName().ToString()+" );";
            string sql = "Select * From " + t.TableName().ToString();
            Reader = ExcuteQuery(sql);
            sql = "Delete From " + new T().TableName().ToString() + " Where " + Reader.GetName(0) + "=" + t.OnlyID.ToString();
            ExcuteQuery(sql);
        }
        public void UpdateByOnlyId<T>(T t) where T : BaseTableMode, new()
        {
            string sql = "Select * From " + t.TableName().ToString();
            Reader = ExcuteQuery(sql);
            PropertyInfo[] info = t.GetType().GetProperties();
            sql = "Update " + t.TableName();

            for (int i = 1; i < info.Length - 1; i++)
            {
                if (i == 1)
                {
                    if (info[i].PropertyType == typeof(String))
                    {
                        sql += " Set " + Reader.GetName(i) + " = '" + info[i].GetValue(t) + "',";

                    }
                    else
                    {
                        sql += " Set " + Reader.GetName(i) + " = " + info[i].GetValue(t) + ",";
                    }
                }
                else
                {
                    if (info[i].PropertyType == typeof(String))
                    {
                        sql += Reader.GetName(i) + " = '" + info[i].GetValue(t) + "',";

                    }
                    else
                    {
                        sql += Reader.GetName(i) + " = " + info[i].GetValue(t) + ",";
                    }

                }

            }
            if (info[info.Length - 1].PropertyType == typeof(String))
            {
                sql += Reader.GetName(info.Length - 1) + " = '" + info[info.Length - 1].GetValue(t) + "'";

            }
            else
            {
                sql += Reader.GetName(info.Length - 1) + " = " + info[info.Length - 1].GetValue(t);
            }

            sql += " Where " + Reader.GetName(0) + " = " + t.OnlyID + ";";
            ExcuteQuery(sql);
        }
        public bool SearchByOnlyId<T>(ref T t) where T : BaseTableMode, new()
        {
            string sql = "Select * From " + t.TableName().ToString();
            Reader = ExcuteQuery(sql);

            sql = "Select * From " + t.TableName().ToString() + " Where " + Reader.GetName(0) + " = " + t.OnlyID;
            Reader = ExcuteQuery(sql);
            PropertyInfo[] info = t.GetType().GetProperties();
            if (!Reader.Read()) return false;
            for (int i = 0; i < info.Length; i++)
            {
                info[i].SetValue(t, Reader.GetValue(i));
            }
            return true;
        }
        public void ReadTable<T>() where T : BaseTableMode, new()
        {
            T t = new T();
            t.Dictionary<T>().Clear();
            PropertyInfo[] info = t.GetType().GetProperties();
            string sql = "Select * From " + t.TableName().ToString();
            Reader = ExcuteQuery(sql);
            while (Reader.Read())
            {
                T tt = new T();

                for (int i = 0; i < info.Length; i++)
                {
                    info[i].SetValue(tt, Reader.GetValue(i));
                }
                tt.Dictionary<T>().Add(tt.OnlyID, tt);
            }
        }
        public void ReadTable<T>(T t) where T : BaseTableMode, new()
        {
            t.Dictionary<T>().Clear();
            PropertyInfo[] info = t.GetType().GetProperties();
            string sql = "Select * From " + t.TableName().ToString();
            Reader = ExcuteQuery(sql);
            while (Reader.Read())
            {
                T tt = new T();

                for (int i = 0; i < info.Length; i++)
                {
                    info[i].SetValue(tt, Reader.GetValue(i));
                }
                tt.Dictionary<T>().Add(tt.OnlyID, tt);
            }
        }
        public int GetTableRecordCount<T>() where T : BaseTableMode, new()
        {
            string sql = "Select count(*)  From " + new T().TableName().ToString();
            return (int)ExcuteQuery(sql).GetValue(0);
        }
        public bool ExistTable<T>() where T : BaseTableMode, new()
        {
            string sql = "SELECT COUNT(*) as CNT FROM sqlite_master WHERE type = 'table' AND name = '" + new T().TableName() + "';";
            return (long)ExcuteQuery(sql).GetValue(0) > 0 ? true : false;
        }
        public bool ExistTable(Type type)
        {
            string sql = "SELECT COUNT(*) as CNT FROM sqlite_master WHERE type = 'table' AND name = '" + MYReFlection.CreateInstance<BaseTableMode>(type).TableName() + "';";
            return (long)ExcuteQuery(sql).GetValue(0) > 0 ? true : false;
        }
        public int TableCount()
        {
            string sql = "SELECT COUNT(*) as CNT FROM sqlite_master WHERE type = 'table' ;";
            return (int)ExcuteQuery(sql).GetValue(0);
        }
        public List<string> GetAllTableName()
        {
            string sql = " SELECT name  FROM sqlite_master WHERE type = 'table';";
            List<string> tableNames = new List<string>();
            Reader = ExcuteQuery(sql);
            while (Reader.Read())
            {
                tableNames.Add(Reader.GetValue(0).ToString());
            }
            return tableNames;
        }
       
        public void ExcuteDB(Action action, bool isCloseDB=false)
        {
       
            ConnectDB();
            if (action!=null)
            {
                action();
            }
            if (isCloseDB)
            {
                CloseDB();
            }
        }
    }
}
